#-*- encoding: utf-8 -*-
import win32com.client 
from Misc.Utils import *

from DataAccess.QueryBlp import *
from DataAccess.DBConnFactory import DBConnFactory

from string import Template
from datetime import date,timedelta

from ReportTool.HldReport import *
from ReportTool.PnLReport import *
from ReportTool.NAVReport import *
from ReportTool.MktValReport import *
from ReportTool.TradeReport import *
from ReportTool.RealizedPnL import *
from ReportTool.OnshoreHldReport import *

from Config.ReportConfig import *

def CPPIB_Position(ref_date):

	hld = query_hld(ref_date, portf_list=['SC032'])
	
	tplt_file = TPLT_PATH + 'Position file_NT9S.xls'
	xApp = win32com.client.Dispatch("Excel.Application")
	xBook = xApp.Workbooks.Open(tplt_file)
	xSht = xBook.Worksheets('Report')
	
	blp_tick = []
	rcd = {}
	for elem in hld:
		if elem[2] == 'CASH' or elem[2] == 'REPO':
			continue
		else:
			blp_ticker = ch_ticker_to_blp_ticker(elem[0])
			rcd[blp_ticker] = [elem[4], elem[7], elem[9]]
			blp_tick.append(blp_ticker)
	
	sec_name_dict = query_blp_static(blp_tick, ['NAME'])
	sec_isin_dict = query_blp_static(blp_tick, ['ID_ISIN'])

	row = 2
	for tick in rcd.keys():
	
		xSht.Cells(row, 1).Value = ref_date
		xSht.Cells(row, 2).Value = 'NT9S'
		xSht.Cells(row, 3).Value = sec_isin_dict[tick]['ID_ISIN']
		xSht.Cells(row, 4).Value = sec_name_dict[tick]['NAME']
		xSht.Cells(row, 5).Value = rcd[tick][0]
		xSht.Cells(row, 6).Value = rcd[tick][1]
		xSht.Cells(row, 7).Value = rcd[tick][1]
		xSht.Cells(row, 8).Value = rcd[tick][2]
		
		row += 1
		
	rept_file = REPT_PATH + 'Position file_NT9S_' + ref_date.strftime("%Y%m%d") + '.xls'
	xBook.SaveAs(rept_file)
	xBook.Close()
	del xApp
	
	print 'info, cppib position report completed at ', ref_date.isoformat()